<?php 
class product_models_admin extends CI_Model{
	public function __construct(){
		$this->load->database();
	}
	public function product($id){
		
	}
   public function create($product){
   	
   		$product_photo=$product->photo;
   		unset($product->photo);
   		$product_options=$product->option;
	   	unset($product->option);
	   	
   		$product->prd_link=self::valide_link($product->prd_link);
   		//creer produit
	   	$this->db->insert('product', $product);
	   	//prendre id auto increment
	   	$product->prd_id=$this->db->insert_id();
	   	//ajout option pour les filtre	   	
	   	if(isset($product_options) and !empty($product_options)){
		   		foreach ($product_options as $option_id=>$valeur_option){
		   			$product_option[]=array(
		   					'prd_id'=>$product->prd_id,
		   					'option_id'=>$option_id,
		   					'valeur_option'=>$valeur_option,
		   					'numeric_option'=>is_numeric($valeur_option)?$valeur_option:null
		   			);
		   		}
		   		$this->db->insert_batch('product_option', $product_option);
	   	}
		//ajout photo
	   	if(!empty($product_photo)){
	   		foreach ($product_photo as $photoName){
	   			$photo[]=array(
	   					'prd_id'=>$product->prd_id,
	   					'link_photo'=>$photoName);
	   		}
	   		$this->db->insert_batch('product_photo', $photo);
	   		
	   	}
	   	
   		return 	$product->prd_id;
   }
    public function get_month_year_create($limit=''){  
    	if($limit) $limit=" LIMIT $limit";  	
		$get= $this->db->query("SELECT DISTINCT DATE_FORMAT(DATE( FROM_UNIXTIME( date_validate ) ),'%Y%m' )AS YearMonth,
		    	DATE_FORMAT(DATE( FROM_UNIXTIME( date_validate ) ),'%m' )AS month,
		    	DATE_FORMAT(DATE( FROM_UNIXTIME( date_validate ) ),'%Y' )AS year
				from product  $limit");
		return $get->result();
    
    }
    public function get_info_product($prd_id){
    	if(empty($prd_id)) return false;
    	$query = $this->db->get_where('product', array('prd_id' => $prd_id), 1);
    	$product=new stdclass;
    	if($query->num_rows()){
    		$row= $query->result();
    		$product=$row[0];
    		$category=$this->load->module('category/admin');
    		$product->link_photo='no-photo.gif';
    		$product->link_cat_product=$category->get_link_category($product->category_id);

    		$product->category_name=$category->get_name_category($product->category_id);
    		
    		//si il y a des options
    		$query = $this->db->get_where('product_option', array('prd_id' => $prd_id));
    		if($query->num_rows()){
    			foreach ($query->result() as $row){
    				$product->option[$row->option_id]=$row->valeur_option;
    			}
    		}
    		//si il y a des photos
    		$query = $this->db->get_where('product_photo', array('prd_id' => $prd_id));
    		if($query->num_rows()){
    			$i=0;
    			foreach ($query->result() as $row){
    				$product->photo[$row->id]=$row->link_photo;
    				if(!$i++)$product->link_photo=$row->link_photo;
    			}
    		}
    		$query = $this->db->get_where('departement_ville', array('Cp' => $product->prd_codepostal), 1);
    		$row= $query->result();
    		$product->ville='';
    		if(isset($row[0])) $product->ville=$row[0]->N_Com_min;
    		return $product;
    	}
    	return false;
    }
    // si option est valeur à saissir Min Max on change un table
    /*
     * exeple: un voiture annee min 1990 annee max 2000
     * option[1]=1990 option[2]=2000
     * parent de option 1 et 2 est 80 
     * donc option[80]= array(1990,2000)
     * */
    private function generateOptionMinMax($options) {
    		foreach ($options as $id_type=>$option){
    			$parent =self::get_parentOption($id_type);
    			if($parent){
    				$options[$parent][]=$option;
    				unset($options[$id_type]);
    			}
    			
    		}
    		//sort min max
    		foreach ($options as $id_type=>$option){
    			if(is_array($option)){
    				sort($option);
    				if(isset($option[0]) and empty($option[0])) unset($option[0]);
    				if(isset($option[1]) and empty($option[1])) unset($option[1]);
    				if(!empty($option)) $options [$id_type]=$option;
    			}
    			if(empty($option)) unset($options [$id_type]);
    		}
    		return $options;
    } 
    /* generate SQL pour tous les options
     * 
     * */
    private function generateOptionSQL($options) {
    		if(!empty($options))
	    		foreach ($options as $id_type=>$option){
	    			$champOptionValue=is_numeric($option)?'numeric_option':'valeur_option';
	    			if(is_array($option)){
	    				if( isset($option[0]) and $option[0]  ) {
	    					$sql="(SELECT prd_id FROM product_option WHERE $champOptionValue > '{$option[0]}')";
	    				}
	    				if( isset($option[1]) and $option[1]  ) {
	    					$sql="(SELECT prd_id FROM product_option WHERE $champOptionValue < '{$option[1]}')";
	    				}
	    				if( isset($option[0]) and $option[0] and
	    					isset($option[1]) and $option[1] ) {
	    					$sql="(SELECT prd_id FROM product_option WHERE $champOptionValue BETWEEN '{$option[0]}' and '{$option[1]}')";
	    				}
	    					
	    			}else{
	    				$sql="(SELECT prd_id FROM product_option WHERE $champOptionValue='".addslashes($option)."')";
	    			}
	    			$alias_table[]=$sql;
	    		}
    		$from='';
    		if(!empty($alias_table))
	    		foreach($alias_table as $index=>$sqlT){
	    			$from.=$sqlT." T$index ";
	    			
	    			if($index) $from.= " \n ON T0.prd_id= T$index.prd_id ";
	    			
	    			if($index!=count($alias_table)-1) $from.=" \n INNER JOIN ";
	    			
	    		}
    		if($from) return $select= "SELECT T0.prd_id  FROM $from";
    		return '';
    } 
    /*
     * recuperer id parent apres on remplacer id actuelle par id parent
     * retourner false si 0
     * */
    private function get_parentOption($id_type) {
    	$query = $this->db->get_where('options', array('id_type' => $id_type), 1);
    	if($query->num_rows() ){
    		$row=$query->result();
    		return $row[0]->parent;
    	}
    	return false;
    }
    
    /*
     * créer une table temporaire pour stocker tous prd_id
     * retourner false si 0
     * */
    private function set_tableTemp($nameTempTable) {
    	$sql = "CREATE TEMPORARY TABLE IF NOT EXISTS `$nameTempTable`
		   (`id` INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
		   `prd_id` INT(10) NOT NULL) ENGINE = MyISAM";
		$this->db->query($sql);  
    }
    private function set_tableWordTemp($nameTempTable) {
    	$sql = "CREATE TEMPORARY TABLE IF NOT EXISTS `$nameTempTable`
		   (`id_doc` INT(10) NOT NULL  PRIMARY KEY, 
		   `total` INT(8) NOT NULL) ENGINE = MyISAM";
		$this->db->query($sql);  
    }
    

    /*
     * supprime une table temporaire 
    * retourner false si 0
    * */
    private function drop_tableTemp($nameTempTable) {
    	$sql = "DROP TEMPORARY TABLE IF  EXISTS `$nameTempTable`";
    	$this->db->query($sql);
    }

    public function get_list_product($filter=array(),$options=array(),$sort=array(),$word='',$paging=array(),$is_count=false){
    	
    	//check option vide
    	if(!empty($options))
	    	foreach ($options as $index=>$option) {
	    		if(trim($option)=='' or $option==0) unset($options[$index]);
	    	}
    	//check filter vide
    	if(!empty($filter))
	    	foreach ($filter as $index=>$option) {
    			if(empty($option))unset($filter[$index]);
	    		if(!is_array($option) and trim($option)=='' or $option==0) unset($filter[$index]);
	    	}

    	if(!empty($options)){
    		$options=self::generateOptionMinMax($options);
    		$optionSQL=self::generateOptionSQL($options);
    		 
    		//creer table temp pour stocker les id
    		$tableTemp= substr(str_shuffle("0123456789abcdefghijklmnopqrstuvwxyz"), 0, 10);
    		self::set_tableTemp($tableTemp);
    		//remplir le table temporaire
    		 
    		$this->db->query($sql="INSERT INTO $tableTemp (prd_id) ( $optionSQL )");
    	}

    	if($word!=''){
    		$sql_indexation=self::search_by_words($word);
    		$tableWordTemp= substr(str_shuffle("0123456789abcdefghijklmnopqrstuvwxyz"), 0, 10);
    		self::set_tableWordTemp($tableWordTemp);
    		$sql="INSERT INTO $tableWordTemp (id_doc,total) ( $sql_indexation )";
    		$this->db->query($sql);
    		
    	}
    	
    	if(!empty($filter)){

    		if(isset($filter['category'])){
    			//si parent on cherche sous category
    			$get_list_category_id=self::get_sub_category($filter['category']);
    			$this->db->where_in('category_id',explode(',',$get_list_category_id));
    		}
    		if(isset($filter['prd_active']))
    			$this->db->where('prd_active', $filter['prd_active']);
    		
    		if(isset($filter['prd_type']))
    			$this->db->where('prd_type',$filter['prd_type']);
    		
    		if(isset($filter['company']))
    			$this->db->where('company',$filter['company']);
    		
    		if(isset($filter['shop_id']))
    			$this->db->where('shop_id',$filter['shop_id']);
    		
    		if(isset($filter['departement'])){
    			if(is_array($filter['departement']))
    			$this->db->where_in('prd_departement',$filter['departement']);
    		}
    		if(isset($filter['date']) )
    			$this->db->where("DATE_FORMAT(DATE( FROM_UNIXTIME(product.date_validate ) ),'%Y%m' )='{$filter['date']}'");

    		if(isset($filter['prd_price']) ){
    			if(is_array($filter['prd_price']) and isset($filter['prd_price']['min']) and $filter['prd_price']['min']) $min=$filter['prd_price']['min'];
    			if(is_array($filter['prd_price']) and isset($filter['prd_price']['max']) and $filter['prd_price']['max']) $max=$filter['prd_price']['max'];
    			if(isset($min)){
    				if(isset($max))
    					$this->db->where("prd_price BETWEEN $min AND $max ");
    				else
    					$this->db->where('prd_price >=',$min);
    			}elseif(isset($max))
    					$this->db->where('prd_price <=',$max);
    		}
    		
    	}
    	
		if(isset($tableWordTemp)) {
			$this->db->join($tableWordTemp, "$tableWordTemp.id_doc = product.prd_id");
		}

		if(isset($tableTemp)){
			$this->db->join($tableTemp, "$tableTemp.prd_id = product.prd_id");
		}
		
		
		$ar_join=$this->db->ar_join;
		$ar_where=$ar_where_company=$this->db->ar_where;
		//count particuliers et professionels
	
		if(isset($filter['company']))
			foreach ($ar_where_company as $index=>$find_where_company)
				if($find_where_company=='AND `company` =  0' or $find_where_company=='AND `company` =  1')
					unset($ar_where_company[$index]);
				
		$this->db->select('count(DISTINCT product.prd_id) as total');
		$this->db->from('product');
		$this->db->ar_where=$ar_where_company;
		$get = $this->db->get();
		$row=$get->result();
		$this->load->library('session');
		$this->session->set_userdata(array('total_annonce'=>$row[0]->total));		

		$this->db->ar_join=$ar_join;
		$this->db->ar_where=$ar_where_company;
		$this->db->select('count(DISTINCT product.prd_id) as total');
		$this->db->from('product');
		$this->db->where('company',0);
		$get = $this->db->get();
		$row=$get->result();
		$this->session->set_userdata(array('total_particuliers'=>$row[0]->total));
		
		$this->db->ar_join=$ar_join;
		$this->db->ar_where=$ar_where_company;
		$this->db->select('count(DISTINCT product.prd_id) as total');
		$this->db->from('product');
		$this->db->where('company',1);
		$get = $this->db->get();
		$row=$get->result();		
		$this->session->set_userdata(array('total_professionels'=>$row[0]->total));	

		//reset query after count
		$this->db->ar_join=$ar_join;
		$this->db->ar_where=$ar_where;
		
		
		if($paging!=''){
			$limit=$paging['number_affiche'];
			$current=$paging['page_current'];
			if($current=="")$current=0;
			$this->db->limit($limit,$current);
		}
		 
		if(!empty($sort))
		foreach ($sort as $sortName=>$valueSort)
			$this->db->order_by($sortName,$valueSort);

		if(isset($tableWordTemp)) {
			$this->db->order_by("$tableWordTemp.total","DESC");
		}
		
		$this->db->select('product.prd_id');
		$this->db->distinct();
		 
		$this->db->from('product');

		$get = $this->db->get();
		

		//enlever temp table apres avoir les resultat
		if(isset($tableTemp)){
			self::drop_tableTemp($tableTemp) ;
		}
		if(isset($tableWordTemp)) {
			self::drop_tableTemp($tableWordTemp) ;
		}
		
		return $get->result();
    
    }
    public function get_total_product($active=FALSE){
    	if($active) $active=" WHERE prd_active=1";
    	else $active='';
    	$get= $this->db->query("SELECT count(*) as numProduct FROM product $active");
    	$row=$get->result();
    	
    	return $row[0]->numProduct;
    }

    public function delete($id){
    	$this->db->delete('product', array('prd_id' => $id));
    }
    public function deactive($id){
    	$this->db->update('product', array('prd_active'=>0), "prd_id = $id");
    }
    public function reactive($id){
    	$this->db->update('product', array('prd_active'=>1), "prd_id = $id");
    }
    public function delPhoto($idPhoto){
    	$arrayId=array();
    	if(!is_array($idPhoto))$arrayId[]=$idPhoto;
    	else $arrayId=$idPhoto;
    	foreach($arrayId as $id){
    		$this->db->delete('product_photo', array('id' => $id));
    	}
    }
    public function edit($product){
    	//ajout option pour les filtre
    	if(isset($product->option) and !empty($product->option)){
    		foreach ($product->option as $option_id=>$valeur_option){
    			$product_options[]=array(
    					'prd_id'=>$product->prd_id,
    					'option_id'=>$option_id,
    					'valeur_option'=>$valeur_option,
    					'numeric_option'=>is_numeric($valeur_option)?$valeur_option:null
    			);
    		}
    		if(isset($product_options)){
    			foreach ($product_options as $product_option){
    				$query = $this->db->get_where('product_option', 
    										array(
	    										'prd_id' => $product_option['prd_id'],
	    										'option_id' => $product_option['option_id']
    										)
    					);
    				if($query->num_rows()){

    					$this->db->update('product_option',$product_option,
    										array(
	    										'prd_id' => $product_option['prd_id'],
	    										'option_id' => $product_option['option_id']
    										)
    					);
    				}else{

    					foreach ($product_options as $option){
    						if($option['valeur_option']!=null and  $option['numeric_option']!=null)
    							$product_option_add[]=$product_option;
    					}
    				}
    			}
    			if(isset($product_option_add)) 
    				$this->db->insert_batch('product_option', $product_option_add);
    		}
    		unset($product->option);
    	}
    	//ajout photo
    	if(isset($product->photo)){
    		if(!empty($product->photo)){
    			
	    		foreach ($product->photo as $photoName){
	    			$product_photo[]=array(
	    					'prd_id'=>$product->prd_id,
	    					'link_photo'=>$photoName);
	    		}
	    		$this->db->insert_batch('product_photo', $product_photo);
    		}
    		unset($product->photo);
    	}
    	//generate link
    	if(isset($product->prd_link)){
    		$product->prd_link=self::valide_link($product->prd_link);
    	}else{
    		$this->load->helper(  'url'  );
    		$this->load->helper( 'text' );
    		$temp_link= url_title(convert_accented_characters($product->prd_name));
    		$checkUrl=self::get_page_link($temp_link);
    		if($checkUrl){
    			if($checkUrl->prd_id!=$product->prd_id)$product->prd_link=$temp_link;
    			if($checkUrl->prd_link=='') $product->prd_link=$temp_link;
    		}else{
    			$product->prd_link=$temp_link;
    		}
    	}
    	$this->db->update('product', $product, "prd_id = {$product->prd_id}");


    }
    private function get_page_link($page_link){
    	$query = $this->db->get_where('product', array('prd_link' => $page_link), 1);
    	
    	if($query->num_rows()){
    		$row=$query->result();
    		return $row[0];
    	}
    	return false;
    }
    private function valide_link($page_link){
    	if(self::get_page_link($page_link)){
    		$random=substr(str_shuffle("0123456789abcdefghijklmnopqrstuvwxyz"), 0, 4);
    		return $page_link.'-'.$random;
    	}
    	return $page_link;
    }
    

    public function search_by_words($rechercher){
    	//separer mot par mot
    	if(trim($rechercher)!='')
    	foreach (explode(' ',$rechercher) as $word){
    		if(strlen ($word)>2)
    			$keyword[$word]=implode('',self::mbStringToArray($word));
    	}
    	 
    	if(isset($keyword)){
    		foreach ($keyword as $word=>$sortWord){
    			$query = $this->db->get_where('index_product_word', array('word' => $word));
    			//si le mot est existe si non recherche faute d'othographe
    			if( $query->num_rows()){
    				foreach ( $query->result() as $row)
    					$id_word[$word]= $row->id;
    			}else{
    				$this->db->select('id')->from('index_product_word')->where_in('sort',$keyword);
    				$query = $this->db->get();
    				foreach ( $query->result() as $row)
    					$id_word[$word]= $row->id;
    			}
    		}
    
    		 
    	}
    	//chercher le mot dans product trier par son poids
    	if(isset($id_word)){
    		$id_word=array_unique($id_word);
    		return $sql='Select id_doc, sum(weight) as total
    				From index_word 
    				Where id_word in ('.implode(',',$id_word).') 
    				Group By id_doc
    				Order by total DESC';
    	}
    	return  '';
    }
    private function mbStringToArray( $string){
    	$string = htmlentities($string, ENT_NOQUOTES, 'utf-8');
    	 
    	// remplacer les entités HTML pour avoir juste le premier caractères non accentués
    	// Exemple : "&ecute;" => "e", "&Ecute;" => "E", "Ã " => "a" ...
    	$string = preg_replace('#&([A-za-z])(?:acute|grave|cedil|circ|orn|ring|slash|th|tilde|uml);#', '\1', $string);
    	// Supprimer tout le reste
    	$string = preg_replace('#&[^;]+;#', '', $string);
    	$sort_word=str_split($string);
    	sort($sort_word);
    	return $sort_word;
    }
    private function get_sub_category($category){
    	$this->db->select('id')->from('category')->where('parent',$category);
    	$query = $this->db->get();
    	if($query->num_rows())
    		foreach ( $query->result() as $row)
    			$id_cat[]= $row->id;
    	if(isset($id_cat)) return implode(',',$id_cat);
    	return $category;
    }
}